package com.mb.app.appused.data;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.json.JSONException;
import org.json.JSONObject;

import com.google.gson.Gson;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;;

/**
 * 
 * 数据库控制
 * 
 * @author xhwwzd
 * @version 1.0
 * @time 2016-08-23
 *
 */
public class DBController<T> {

	private Context context;
	private String dbName;
	private Class<T> myClass;
	DBHelper helper;
	int VERSION = 1;

	// 比较方式
	public enum CompareType {
		EQ, LESS, GREATER, NEQ, LIKE, EGEATER, ELESS
	}

	// 链接方式
	public enum Condistion {
		AND, OR
	}

	// 排序方式
	public enum OrderType {
		DESC, ASC
	};

	public DBController(Context context, String dbName, Class<T> myclass) {
		this.context = context;
		this.dbName = dbName;
		this.myClass = myclass;
		creat();
	}

	public DBController(Context context, String dbName, int version, Class<T> myclass) {
		this.context = context;
		this.dbName = dbName;
		this.myClass = myclass;
		this.VERSION = version;
		creat();
	}

	/**
	 * 创建表格
	 * 
	 * @return
	 */
	boolean creat() {
		if (myClass == null)
			return false;
		helper = new DBHelper(this.context, dbName + ".sqlite", null, VERSION) {
			@Override
			String getCreatSQL() {
				// TODO Auto-generated method stub
				// return Instance.getCreatSQL();
				return null;
			}

			@Override
			String getonUpgradeSQL() {
				// TODO Auto-generated method stub
				return null;
			}
		};
		creatTable();
		return false;
	}

	/**
	 * 创建表格
	 * 
	 * @return
	 */
	void creatTable() {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = getCreatTable();
		db.execSQL(sql);
		db.close();
	}

	/**
	 * 创建表格 sql语句
	 * 
	 * @return
	 */
	String getCreatTable() {
		String sql = "";
		sql = "create table if not exists {tableName}( id integer primary key autoincrement,";
		sql = sql.replace("{tableName}", myClass.getSimpleName());

		for (Field temp : myClass.getFields()) {
			if (temp.getName().compareTo("id") == 0)
				continue;
			sql += temp.getName() + " " + getType(temp) + ",";
		}
		sql += ")";
		sql = sql.replace(",)", ")");
		return sql;
	}

	/**
	 * 获取数据类型
	 * 
	 * @param temp
	 * @return
	 */
	String getType(Field temp) {
		String type = "";
		Log.e("Tyep", temp.getType().getSimpleName());
		if (temp.getType().getSimpleName().compareTo("int") == 0) {
			type = "interger";
		}
		if (temp.getType().getSimpleName().compareTo("String") == 0) {
			type = "text";
		}
		if (temp.getType().getSimpleName().compareTo("boolean") == 0) {
			type = "interger";
		}

		if (temp.getType().getSimpleName().compareTo("Integer") == 0) {
			type = "interger";
		}
		if (temp.getType().getSimpleName().compareTo("char") == 0) {
			type = "char(255)";
		}
		if (temp.getType().getSimpleName().compareTo("char[]") == 0) {
			type = "varchar(" + 256 + ")";
		}
		return type;
	}

	/**
	 * 获取表格名称
	 * 
	 * @param t
	 * @return
	 */
	public String getTableName() {
		return myClass.getSimpleName();
	}

	/**
	 * 获取书库名称
	 * 
	 * @return
	 */
	public String getDbName() {
		return dbName;
	}

	/**
	 * 获取书库版本
	 * 
	 * @return
	 */
	public int getVERSION() {
		return VERSION;
	}

	/**
	 * 添加数据
	 * 
	 * @param t
	 */
	public void add(T t) {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "insert into {tableName} ({file}) values ({temp})";
		sql = sql.replace("{tableName}", getTableName());

		String file = "";
		String value = "";
		for (Field temp : t.getClass().getFields()) {
			try {

				if (getActive(temp.get(t))) {
					file += " " + temp.getName() + " ,";
					value += " '" + temp.get(t).toString().replace("'", "\'") + "' ,";
				}

			} catch (IllegalAccessException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (IllegalArgumentException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}

		}
		file = file.substring(0, file.length() - 1);
		value = value.substring(0, value.length() - 1);

		sql = sql.replace("{file}", file);
		sql = sql.replace("{temp}", value);
		Log.e("info", "add sql:" + sql);
		db.execSQL(sql);
		db.close();
	}

	/**
	 * 更新数据
	 * 
	 * @param where
	 *            原始数据
	 * @param data
	 *            新数据
	 */
	public void update(T where, T data) {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "update {table} set ";
		sql = sql.replace("{table}", getTableName());

		ObjectInfo<T> d = new ObjectInfo<T>(where);
		ObjectInfo<T> c = new ObjectInfo<T>(data);
		c.compare = CompareType.EQ;

		sql += d.getLinkInfo(",");
		sql += " where " + c.getInfo();

		Log.e("info", sql);

		db.execSQL(sql);
		db.close();
	}

	/**
	 * 移除数据
	 * 
	 * @param t
	 */
	public void remove(T t) {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "delete from {table} where {temp}";
		sql = sql.replace("{table}", getTableName());
		String where = "";
		List<CustomColumn> columns = new ArrayList<CustomColumn>();
		for (Field temp : t.getClass().getFields()) {
			try {
				if (getActive(temp.get(t))) {
					CustomColumn col = new CustomColumn();
					col.name = temp.getName();
					col.value = temp.get(t).toString().replace("'", "\'");
					columns.add(col);
				}
			} catch (IllegalAccessException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (IllegalArgumentException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		where = Colum2Condistion(CompareType.EQ, Condistion.AND, columns);
		sql = sql.replace("{temp}", where);
		Log.e("info", sql);
		db.execSQL(sql);
		db.close();
	}

	/**
	 * 查询所有数据
	 * 
	 * @return
	 */
	public List<T> findAll() {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "select * from {table} ";
		sql = sql.replace("{table}", getTableName());
		List<T> Result = new ArrayList<T>();
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			JSONObject obj = new JSONObject();
			for (int sp = 0; sp < cursor.getColumnCount(); sp++) {
				try {
					obj.put(cursor.getColumnName(sp), cursor.getString(sp));
				} catch (JSONException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			cursor.moveToNext();
			Result.add(creatBean(obj));
		}
		cursor.close();
		db.close();
		return Result;
	}

	/**
	 * 查询
	 */
	public List<T> find(T t) {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "select * from {table} where {temp} ";
		sql = sql.replace("{table}", getTableName());
		String where = "";
		List<CustomColumn> columns = new ArrayList<CustomColumn>();
		
		for (Field temp : t.getClass().getFields()) {
			try {
				if (getActive(temp.get(t))) {
					CustomColumn col = new CustomColumn();
					col.name = temp.getName();
					col.value = temp.get(t).toString().replace("'", "\'");
					columns.add(col);
				}
			} catch (IllegalAccessException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (IllegalArgumentException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		where = Colum2Condistion(CompareType.EQ, Condistion.AND, columns);
		sql = sql.replace("{temp}", where);
		Log.e("info", sql);

		List<T> Result = new ArrayList<T>();
		Cursor cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			JSONObject obj = new JSONObject();
			for (int sp = 0; sp < cursor.getColumnCount(); sp++) {
				try {
					obj.put(cursor.getColumnName(sp), cursor.getString(sp));
				} catch (JSONException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			cursor.moveToNext();
			Result.add(creatBean(obj));
		}
		cursor.close();
		db.close();
		return Result;
	}

	/**
	 * 查数据
	 * 
	 * @param condistion
	 * @param infos
	 */
	public List<T> select(Condistion condistion, ColumnsInfo... infos) {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "select * from {table} where {temp} ";
		sql = sql.replace("{table}", getTableName());
		
		String where = "";
		for (ColumnsInfo item : infos) {
			where += " {c} " + item.getColumnInfo() + " ";
		}
		where = getCondistionCode(where, "{c}", condistion);
		where = where.substring(5, where.length());
		sql = sql.replace("{temp}", where);

		Log.e("select", sql);

		Cursor cursor = db.rawQuery(sql, null);
		List<T> Result = new ArrayList<T>();

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			JSONObject obj = new JSONObject();
			for (int sp = 0; sp < cursor.getColumnCount(); sp++) {
				try {
					obj.put(cursor.getColumnName(sp), cursor.getString(sp));
				} catch (JSONException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			cursor.moveToNext();
			Result.add(creatBean(obj));
		}
		cursor.close();
		db.close();
		return Result;
	}

	/**
	 * 查数据
	 * 
	 * @param condistion
	 * @param infos
	 */
	public List<T> select(Condistion condistion, ObjectInfo<T>... infos) {
		SQLiteDatabase db = helper.getWritableDatabase();
		String sql = "select * from {table} where {temp} ";
		sql = sql.replace("{table}", getTableName());
		String where = "";
		for (ObjectInfo<T> item : infos) {
			where += " {c} " + item.getInfo() + " ";
		}
		where = getCondistionCode(where, "{c}", condistion);
		where = where.substring(5, where.length());
		sql = sql.replace("{temp}", where);

		Log.e("select", sql);

		Cursor cursor = db.rawQuery(sql, null);
		List<T> Result = new ArrayList<T>();

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			JSONObject obj = new JSONObject();
			for (int sp = 0; sp < cursor.getColumnCount(); sp++) {
				try {
					obj.put(cursor.getColumnName(sp), cursor.getString(sp));
				} catch (JSONException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			cursor.moveToNext();
			Result.add(creatBean(obj));
		}
		cursor.close();
		db.close();
		return Result;
	}

	/**
	 * 运行sql
	 * 
	 * @param sql
	 */
	public void runSQL(String sql) {
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		cursor.close();
		db.close();
	}

	/**
	 * 是否为条件
	 * 
	 * @param obj
	 * @return
	 */
	@SuppressWarnings("unused")
	static boolean getActive(Object obj) {
		if (obj == null)
			return false;
		if (obj instanceof Integer) {
			int value = ((Integer) obj).intValue();
			if (value < 0)
				return false;
		} else if (obj instanceof String) {
			String s = (String) obj;
		} else if (obj instanceof Double) {
			double d = ((Double) obj).doubleValue();
		} else if (obj instanceof Float) {
			float f = ((Float) obj).floatValue();
		} else if (obj instanceof Long) {
			long l = ((Long) obj).longValue();
		} else if (obj instanceof Boolean) {
			boolean b = ((Boolean) obj).booleanValue();
		} else if (obj instanceof Date) {
			Date d = (Date) obj;
		}
		if (obj != null && obj.toString().length() < 1)
			return false;
		if (obj != null && obj.toString().length() > 0)
			return true;
		return false;
	}

	/**
	 * 字段转换为条件
	 * 
	 * @param type
	 * @param colums
	 * @return
	 */
	String Colum2Condistion(CompareType type, Condistion condistion, CustomColumn... colums) {
		String c = "";
		for (CustomColumn col : colums) {
			c += "{c} " + col.name + " {t} '" + col.value.toString() + "'  ";
		}
		c = getLinkCode(c, "{t}", type);
		c = getCondistionCode(c, "{c}", condistion);
		c = c.substring(5, c.length() - 2);
		return c;
	}

	/**
	 * 
	 * @param type
	 * @param colums
	 * @return
	 */
	String Colum2Condistion(CompareType type, Condistion condistion, List<CustomColumn> colums) {
		String c = "";
		if (colums == null || colums.size() < 1)
			return c;

		int size = colums.size();
		CustomColumn[] arr = (CustomColumn[]) colums.toArray(new CustomColumn[size]);
		c = Colum2Condistion(type, condistion, arr);
		return c;
	}

	/**
	 * 返回连接符
	 * 
	 * @param values
	 * @param singe
	 * @param type
	 * @return
	 */
	static String getLinkCode(String values, String singe, CompareType type) {
		String info = "";
		// Log.e("link code", values + " -> " + singe);
		if (values == null || values.length() < 1)
			return info;
		if (!values.contains(singe))
			return info;

		switch (type) {
		case LESS:
			info = values.replace(singe, " < ");
			break;
		case EQ:
			info = values.replace(singe, " = ");
			break;
		case EGEATER:
			info = values.replace(singe, " >= ");
			break;
		case ELESS:
			info = values.replace(singe, " <= ");
			break;
		case GREATER:
			info = values.replace(singe, " > ");
			break;
		case LIKE:
			info = values.replace(singe, " like ");
			break;
		case NEQ:
			info = values.replace(singe, " != ");
			break;
		}
		return info;
	}

	/**
	 * 获取比较字符
	 * 
	 * @param type
	 * @return
	 */
	static String getCompareCode(CompareType type) {
		String info = "";
		switch (type) {
		case LESS:
			info = " < ";
			break;
		case EQ:
			info = " = ";
			break;
		case EGEATER:
			info = " >= ";
			break;
		case ELESS:
			info = " <= ";
			break;
		case GREATER:
			info = " > ";
			break;
		case LIKE:
			info = " like ";
			break;
		case NEQ:
			info = " != ";
			break;
		}
		return info;
	}

	static String getCondistionCode(String values, String singe, Condistion type) {
		String info = "";
		if (values == null || values.length() < 1)
			return info;
		if (!values.contains(singe))
			return info;

		switch (type) {
		case AND:
			info = values.replace(singe, " and ");
			break;
		case OR:
			info = values.replace(singe, " or  ");
			break;
		}
		return info;
	}

	/**
	 * 获取条件字符
	 * 
	 * @param type
	 * @return
	 */
	static String getCondistionCode(Condistion type) {
		String info = "";
		switch (type) {
		case AND:
			info = " and ";
			break;
		case OR:
			info = " or  ";
			break;
		}
		return info;
	}

	/**
	 * 生成分页
	 * 
	 * @param count
	 * @param start
	 * @return
	 */
	String getLimit(int count, int start) {
		String sql = " limit {count} Offset {start}";
		sql = sql.replace("{count}", count + "");
		sql = sql.replace("{start}", start + "");
		return sql;
	}

	/**
	 * 排序 排序字段
	 * 
	 * @param lable
	 * @return
	 */
	String getOrder(String lable, OrderType order) {
		String sql = " order by {lable} {order}";
		sql = sql.replace("{lable}", lable);
		switch (order) {
		case DESC:
			sql = sql.replace("{order}", "desc");
			break;
		case ASC:
			sql = sql.replace("{order}", "asc");
			break;
		}
		return sql;
	}

	/**
	 * 生成数据
	 * 
	 * @return
	 */
	T creatBean(JSONObject obj) {
		Gson gson = new Gson();
		T item = (T) gson.fromJson(obj.toString(), myClass);
		return item;
	}

	/**
	 * 数据库操作基类
	 * 
	 * @author xhwwzd
	 *
	 */
	abstract class DBHelper extends SQLiteOpenHelper {
		abstract String getCreatSQL();

		abstract String getonUpgradeSQL();

		public DBHelper(Context context, String name, CursorFactory factory, int version) {
			super(context, name, factory, version);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub

			String sql = getCreatSQL();
			if (sql == null || sql.length() < 1)
				return;
			db.execSQL(sql);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub

		}
	}

	/**
	 * 根据对象生成
	 * 
	 * @author xhwwzd
	 *
	 */
	public static class ObjectInfo<T> {
		public T myObj;
		public Condistion condistion = Condistion.AND;
		public CompareType compare = CompareType.EQ;

		public ObjectInfo(T obj) {
			this.myObj = obj;
		}

		public ObjectInfo() {

		}

		public T getMyObj() {
			return myObj;
		}

		public void setMyObj(T myObj) {
			this.myObj = myObj;
		}

		/**
		 * 获取信息
		 * 
		 * @return
		 */
		public String getInfo() {
			List<CustomColumn> list = new ArrayList<DBController.CustomColumn>();

			for (Field temp : myObj.getClass().getFields()) {
				try {
					if (getActive(temp.get(myObj))) {
						CustomColumn item = new CustomColumn(temp.getName(), temp.get(myObj));
						list.add(item);
					}
				} catch (IllegalAccessException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				} catch (IllegalArgumentException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}

			int size = list.size();
			CustomColumn[] arr = (CustomColumn[]) list.toArray(new CustomColumn[size]);
			ColumnsInfo info = new ColumnsInfo(arr);
			info.compare = compare;
			info.condistion = condistion;
			return info.getColumnInfo();
		}

		/**
		 * 根据条件符，比较符 成组生成内容
		 * 
		 * @param conditionCode
		 * @param compareCode
		 * @param isgroup
		 * @return
		 */
		public String getInfo(String conditionCode, String compareCode, boolean isGroup) {
			List<CustomColumn> list = new ArrayList<DBController.CustomColumn>();

			for (Field temp : myObj.getClass().getFields()) {
				try {
					if (getActive(temp.get(myObj))) {
						CustomColumn item = new CustomColumn(temp.getName(), temp.get(myObj));
						list.add(item);
					}
				} catch (IllegalAccessException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				} catch (IllegalArgumentException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}

			int size = list.size();
			CustomColumn[] arr = (CustomColumn[]) list.toArray(new CustomColumn[size]);
			ColumnsInfo info = new ColumnsInfo(arr);
			info.compare = compare;
			info.condistion = condistion;
			return info.getColumnInfo(conditionCode, compareCode, isGroup);
		}

		/**
		 * 根据指定的连接符
		 * 
		 * @param linkcode
		 * @return
		 */
		public String getLinkInfo(String linkcode) {
			List<CustomColumn> list = new ArrayList<DBController.CustomColumn>();

			for (Field temp : myObj.getClass().getFields()) {
				try {
					if (getActive(temp.get(myObj))) {
						CustomColumn item = new CustomColumn(temp.getName(), temp.get(myObj));
						list.add(item);
					}
				} catch (IllegalAccessException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				} catch (IllegalArgumentException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}

			int size = list.size();
			CustomColumn[] arr = (CustomColumn[]) list.toArray(new CustomColumn[size]);
			ColumnsInfo info = new ColumnsInfo(arr);
			info.compare = compare;
			info.condistion = condistion;
			return info.getColumnLink(linkcode);
		}

		/**
		 * 根据指定的链接符 成组
		 * 
		 * @param linkcode
		 * @return
		 */
		public String getLinkInfoGroup(String linkcode) {
			List<CustomColumn> list = new ArrayList<DBController.CustomColumn>();

			for (Field temp : myObj.getClass().getFields()) {
				try {
					if (getActive(temp.get(myObj))) {
						CustomColumn item = new CustomColumn(temp.getName(), temp.get(myObj));
						list.add(item);
					}
				} catch (IllegalAccessException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				} catch (IllegalArgumentException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}

			int size = list.size();
			CustomColumn[] arr = (CustomColumn[]) list.toArray(new CustomColumn[size]);
			ColumnsInfo info = new ColumnsInfo(arr);
			info.compare = compare;
			info.condistion = condistion;
			return info.getColumnLinkGroup(linkcode);
		}
	}

	/**
	 * 生成条件关系组
	 * 
	 * @author xhwwzd
	 *
	 */
	public static class ColumnsInfo {

		public CustomColumn[] columns;
		public CompareType compare = CompareType.EQ;
		public Condistion condistion = Condistion.AND;

		public ColumnsInfo(CustomColumn... columns) {
			this.columns = columns;
		}

		public void setColumns(CustomColumn... columns) {
			this.columns = columns;
		}

		/**
		 * 根据比较和条件类型生成语句
		 * 
		 * @return
		 */
		public String getColumnInfo() {
			String c = "";

			for (CustomColumn co : columns) {
				c += " {t} " + co.name + " {c} " + "'" + co.value + "' ";
			}

			c = getLinkCode(c, "{c}", compare);

			c = getCondistionCode(c, "{t}", condistion);
			c = c.substring(5, c.length());
			c = "( " + c + " )";

			return c;
		}

		/**
		 * 根据条件符，比较符 成组生成内容
		 * 
		 * @param conditionCode
		 * @param compareCode
		 * @param isgroup
		 * @return
		 */
		public String getColumnInfo(String conditionCode, String compareCode, boolean isgroup) {
			String c = "";

			for (CustomColumn co : columns) {
				c += " {t} " + co.name + " {c} " + "'" + co.value + "' ";
			}

			c = c.replace("{c}", compareCode);
			c = c.replace("{t}", conditionCode);

			// c = getLinkCode(c, "{c}", compare);

			// c = getCondistionCode(c, "{t}", condistion);
			c = c.substring(conditionCode.length() + 1, c.length());
			if (isgroup)
				c = "( " + c + " )";

			return c;
		}

		/**
		 * 根据指定的连接符链接
		 * 
		 * @param linkCode
		 *            连接符 一般使用(,= > < != >= <=)
		 * @return
		 */
		public String getColumnLink(String linkCode) {
			String c = "";

			for (CustomColumn co : columns) {
				c += " {t} " + co.name + " {c} " + "'" + co.value + "' ";
			}

			c = getLinkCode(c, "{c}", compare);

			c = c.replace("{t}", linkCode);
			c = c.substring(linkCode.length() + 1, c.length());
			// c = "( " + c + " )";
			return c;
		}

		/**
		 * 根据组条件返回 连接符一直外出多括弧
		 * 
		 * @param linkCode
		 *            连接符 一般使用(,= > < != >= <=)
		 * @return
		 */
		public String getColumnLinkGroup(String linkCode) {
			String c = getColumnLink(linkCode);
			c = "( " + c + " )";
			return c;
		}
	}

	/**
	 * 字段定义
	 * 
	 * @author xhwwzd
	 *
	 */
	public static class CustomColumn {
		public String name;
		public Object value;

		public CustomColumn() {

		}

		public CustomColumn(String name, Object value) {
			this.name = name;
			this.value = value;
		}
	}

}
